package com.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.handlers.BeanListHandler;

import com.beans.Memberinfo;
import com.jdbc.DBUtil;
import com.utils.PageInfo;
import com.utils.StringUtil;

public class MemberDao {
	private BeanListHandler<Memberinfo> handler=new BeanListHandler<Memberinfo>(Memberinfo.class);
	private String formatStr="yyyy-MM-dd";
	/**
	 * 获取数据条目
	 */
	public int getMemberCount(String memberName,String beginDate,String endDate) {
		StringBuffer sql=new StringBuffer("select count(*) from memberinfo where 1=1 ");
		
		if(StringUtil.isValidDate(beginDate, formatStr)) {
			sql.append(" and registerDate >="+beginDate);
		}
		if(StringUtil.isValidDate(endDate, formatStr)) {
			sql.append(" and registerDate <="+endDate);
		}
		if(!StringUtil.isEmpty(memberName)) {
			sql.append(" and memberNo like '%"+memberName+"%'");
		}
		long count = DBUtil.getScalar(sql.toString());
		return Integer.parseInt(count + "");
	}
	
	/**
	 * 获取会员列表
	 * 
	 * @param page 分页属性
	 */
	public List<Memberinfo> getMemberList(PageInfo page) {
		return DBUtil.getList(
				"select  * from memberinfo order by id desc limit ?, ? ",
				Memberinfo.class,page.getBeginRow(), page.getPageSize());
	}
	
	/**
	 * 根据id获取会员信息
	 * 
	 * @param id 会员id
	 * @return 管理员信息实体类,登录失败返回null
	 */
	public Memberinfo getMember(int id) {
		String sql = "select * from memberinfo where id=?";
		return DBUtil.getSingleObject(sql, Memberinfo.class, id);

	}
	
	/**
	 * 删除会员
	 * 
	 * @param id 角色id
	 * @return
	 */
	public int delMember(int id) {
		return DBUtil.update("delete from memberinfo where id= ?", id);
	}
	
	/**
	 * 批量删除会员用户
	 * 
	 * @param strs 用户id列表
	 * @return
	 */
	public void delAllMember(String[] strs) {
		PreparedStatement stm = null;
		try {
			Connection conn = DBUtil.getConn();
			String sql = "delete from memberinfo where id= ?";
			stm = conn.prepareStatement(sql);

			for (String str : strs) {
				stm.setString(1, str);
				stm.addBatch(); // 添到批处理中了
			}
			stm.executeBatch(); // 执行批处理

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(null, stm);
		}
	}
	
	/**
	 * 多条件复合查询会员
	 * @param bigCateId 会员名称
	 * @param smallCateId 开始时间
	 * @param goodsName 结束时间
	 * @param page 分页
	 * @return
	 */
	public List<Memberinfo> getMemberList(String memberName,String beginDate,String endDate,PageInfo page){
		List<Memberinfo> memberList=new ArrayList<Memberinfo>();
		Statement stm=null;
		ResultSet rs=null;
		
		try {
			Connection conn=DBUtil.getConn();
			stm=conn.createStatement();
			StringBuffer sql=new StringBuffer(" select  * from memberinfo where 1=1 ");
			
			if(StringUtil.isValidDate(beginDate, formatStr)) {
				sql.append(" and registerDate >='"+beginDate+"'");
			}
			if(StringUtil.isValidDate(endDate, formatStr)) {
				sql.append(" and registerDate <='"+endDate+"'");
			}
			if(!StringUtil.isEmpty(memberName)) {
				sql.append(" and memberNo like '%"+memberName+"%'");
			}
			sql.append(" order by id desc limit "+page.getBeginRow()+", "+page.getPageSize());
			rs=stm.executeQuery(sql.toString());
			memberList=(List<Memberinfo>)handler.handle(rs); 

			
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			DBUtil.close(rs,stm);
		}

		return memberList;
	}
}
